package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.log4j.Logger;

public class DbUsers {
    public static Logger LOG = Logger.getLogger("db.DbUsers");

    // check if user is valid
    public static int isValid(String username, String password) {
        int userRole = -1; // this user account is not valid
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            String strQuery = "select m_role from m_users where m_username=N'" + username + "' and m_password=N'"
                    + password + "'";
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement(strQuery);
            rs = pstat.executeQuery();
            if (rs.next()) {
                userRole = rs.getInt("m_role"); // 1: administrator, 2: dictionary manager
            }
        } catch (Exception ex) {
            LOG.error("isValid", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return userRole;
    }

    // change password
    public static boolean changePassword(String username, String password, String newPassword) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstat = null;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("update m_users set m_password=N'" + newPassword + "' where m_username=N'"
                    + username + "' and m_password=N'" + password + "'");
            bOK = (pstat.executeUpdate() > 0);
        } catch (Exception ex) {
            LOG.error("changePassword", ex);
        } finally {
            try {
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex2) {
            }
        }
        return bOK;
    }

    // log user in and update m_date in m_users table
    public static int login(String username, String password) {
        int userRole = -1; // this user account is not valid
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            String strQuery = "select m_role,m_id from m_users where m_username=N'" + username + "' and m_password=N'"
                    + password + "'";
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement(strQuery);
            rs = pstat.executeQuery();
            if (rs.next()) {
                userRole = rs.getInt("m_role"); // 1: administrator, 2: dictionary manager
                int id = rs.getInt("m_id");
                rs.close();
                pstat.close();
                pstat = cnn.prepareStatement("update m_users set m_date=getdate() where m_id=" + id);
                pstat.executeUpdate();
            }
        } catch (Exception ex) {
            LOG.error("login", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return userRole;
    }

    // delete user
    public static boolean deleteUser(String username) {
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        boolean bOK = false;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_id from m_users where m_username=N'" + username + "'");
            rs = pstat.executeQuery();
            if (rs.next()) { // if user exists
                int userID = rs.getInt("m_id");
                rs.close();
                pstat.close();
                // delete from m_approvings table
                pstat = cnn.prepareStatement("delete from m_approvings where m_userid=" + userID);
                pstat.executeUpdate();
                pstat.close();
                // delete from m_managerindict table
                pstat = cnn.prepareStatement("delete from m_managerindict where m_userID=" + userID);
                pstat.executeUpdate();
                pstat.close();
                // delete from m_usrs table
                pstat = cnn.prepareStatement("delete from m_users where m_id=" + userID);
                pstat.executeUpdate();
                pstat.close();
                bOK = true;
            }
        } catch (Exception ex) {
            LOG.error("deleteUser", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    // add new user
    public static boolean addUser(String username, String password, int role) {
        boolean bOK = false;
        Connection cnn = null;
        CallableStatement cstat = null;
        ResultSet rs = null;
        try {
            cnn = DbController.getConnection();
            cstat = cnn.prepareCall("select * from m_users where m_username=N'" + username + "'");
            rs = cstat.executeQuery();
            if (rs.next() == false) {
                rs.close();
                cstat.close();
                cstat = cnn.prepareCall("{call m_users_insert(?, ?, ?)}");
                cstat.setString(1, username);
                cstat.setString(2, password);
                cstat.setInt(3, role);
                bOK = (cstat.executeUpdate() == 1);
            }
        } catch (Exception ex) {
            LOG.error("addUser", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (cstat != null) {
                    cstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    // get userID by name
    public static int getUserIDByName(String name) {
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        int userID = -1;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_id from m_users where m_username=N'" + name + "'");
            rs = pstat.executeQuery();
            if (rs.next()) {
                userID = rs.getInt("m_id");
            }
        } catch (Exception ex) {
            LOG.error("getUserIDByName", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return userID;
    }

}
